package daos.restaurant.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.entities.Restaurant;
import daos.AbstractDAO;
import daos.db.Conexion;
import daos.restaurant.RestaurantAppDAO;

public class RestaurantAppDAOImpl extends AbstractDAO implements
        RestaurantAppDAO {

    Conexion con = new Conexion();

    public void insert(Restaurant restaurant) throws SQLException {

        String insertSql = "INSERT INTO RESTAURANTS (NAME, CITY) VALUES (?,?)";
        PreparedStatement stmt = null;
        Connection conex = null;
        try {

            conex = con.getConnected();
            stmt = conex.prepareStatement(insertSql);

            stmt.setString(1, restaurant.getName());
            stmt.setString(2, restaurant.getCity());
            System.out.println(insertSql);

            stmt.executeUpdate();
            System.out.println("Row inserted OK");

        } finally {
            closeConnection(conex, stmt, null);
        }
    }

    // los metodos son void... siempre se puede borrar salvo q tiren excepcion
    public void delete(Restaurant restaurant) throws SQLException {

        // sql preparado...
        String deleteSql = "DELETE FROM RESTAURANTS WHERE NAME = ?";
        PreparedStatement stmt = null;
        Connection conex = null;
        try {

            conex = con.getConnected();
            stmt = conex.prepareStatement(deleteSql);
            // cambio el signo de pregunta por el parametro correspondiente....
            // en orden..
            // fijate si empieza en 0 si rompe.. pero creo q era en 1
            stmt.setString(1, restaurant.getName());
            System.out.println(deleteSql);
            // se hace un updete en la base...no un excecute
            stmt.executeUpdate();
            System.out.println("Row deleted OK");

            // te saque el catch... estas declarando que tiras exception... si
            // queres loguear la excepcion la
            // capturas arriba y la logueas
        } finally {
            // metodo generico para todos los daos que se encarga de cerrar la
            // coneccion
            closeConnection(conex, stmt, null);
        }
    }

    public void update(Restaurant restaurant) throws SQLException {

        String updateSql = "UPDATE RESTAURANTS SET CITY = ? WHERE NAME = ?";
        PreparedStatement stmt = null;
        Connection conex = null;
        try {

            conex = con.getConnected();
            stmt = conex.prepareStatement(updateSql);
            stmt.setString(1, restaurant.getCity());
            stmt.setString(2, restaurant.getName());

            System.out.println(updateSql);

            stmt.executeUpdate();
            System.out.println("Row updated OK");
        } finally {
            closeConnection(conex, stmt, null);
        }
    }

    public List<Restaurant> selectAll() throws SQLException {
        String selectSql = "SELECT NAME, CITY FROM RESTAURANTS";
        PreparedStatement stmt = null;
        Connection conex = null;
        ResultSet rs = null;
        List<Restaurant> restaurantSeleccionados = null;
        Restaurant restaurantSeleccionado = null;

        try {
            conex = con.getConnected();
            stmt = conex.prepareStatement(selectSql);

            System.out.println(selectSql);

            rs = stmt.executeQuery();

            restaurantSeleccionados = new ArrayList<Restaurant>();

            while (rs.next()) {
                restaurantSeleccionado = new Restaurant();
                restaurantSeleccionado.setName(rs.getString("NAME"));
                restaurantSeleccionado.setCity(rs.getString("CITY"));

                restaurantSeleccionados.add(restaurantSeleccionado);
            }

            return restaurantSeleccionados;

        } finally {
            closeConnection(conex, stmt, rs);
        }
    }

    public List<Restaurant> selectByName(Restaurant restaurant) throws SQLException {
        String selectSql = "SELECT NAME, CITY FROM RESTAURANTS WHERE UPPER(NAME) LIKE ?";
        PreparedStatement stmt = null;
        Connection conex = null;
        ResultSet rs = null;
        List<Restaurant> restaurantSeleccionados = null;
        Restaurant restaurantSeleccionado = null;

        try {
            conex = con.getConnected();
            stmt = conex.prepareStatement(selectSql);
            stmt.setString(1, "%" + restaurant.getName().toUpperCase() + "%");

            System.out.println(selectSql);

            rs = stmt.executeQuery();

            restaurantSeleccionados = new ArrayList<Restaurant>();

            while (rs.next()) {
                restaurantSeleccionado = new Restaurant();
                restaurantSeleccionado.setName(rs.getString("NAME"));
                restaurantSeleccionado.setCity(rs.getString("CITY"));

                restaurantSeleccionados.add(restaurantSeleccionado);
            }

            return restaurantSeleccionados;

        } finally {
            closeConnection(conex, stmt, rs);
        }
    }
    
    public boolean exist(Restaurant restaurant) throws SQLException {
        String selectSql = "SELECT * FROM RESTAURANTS WHERE NAME = ?";
        PreparedStatement stmt = null;
        Connection conex = null;
        ResultSet rs = null;
        try {
            conex = con.getConnected();
            stmt = conex.prepareStatement(selectSql);
            stmt.setString(1, restaurant.getName());

            System.out.println(selectSql);

            rs = stmt.executeQuery();

            rs.next();

            if(rs.getRow() == 0)
            {
                return false;    
            } else {
                return true;
            }
        } finally {
            closeConnection(conex, stmt, null);
        }
    }
}